﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Sac.Data;
using System.Configuration;

namespace Sac.Web
{
    public class MenuTreeOperate
    {
        OledbPro oledb;

        /// <summary>
        /// 构造函数，根据配置文件创建到数据库的连接
        /// </summary>
        public MenuTreeOperate()
        {
            string ConnStr = ConfigurationManager.AppSettings["access"].ToString();
            oledb = new OledbPro(ConnStr);
        }

        /// <summary>
        /// 构造函数，根据连接字符串创建到数据库的连接
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        public MenuTreeOperate(string connStr)
        {
            oledb = new OledbPro(connStr);
        }

        /// <summary>
        /// 获取所有的根菜单
        /// </summary>
        /// <returns>返回包含所有根菜单信息的泛型</returns>
        public List<MenuTree> GetRootMenuList()
        {
            List<MenuTree> RootMenuList = new List<MenuTree>();
            string SqlRoot = "select * from plat_menu_tree where fartherid=-1 order by menu_sort,id_key asc";
            DataTable dtRootMenu = oledb.GetDataTable(SqlRoot);
            if (dtRootMenu != null)
            {
                for (int i = 0; i < dtRootMenu.Rows.Count; i++)
                {
                    DataRow dr=dtRootMenu.Rows[i];
                    MenuTree node = new MenuTree();
                    node.ID_key = Convert.ToInt32(dr["ID_KEY"]);
                    node.Menu_Name = Convert.ToString(dr["MENU_NAME"]);
                    node.FartherId = Convert.ToInt32(dr["FARTHERID"]);
                    node.Menu_Url = Convert.ToString(dr["MENU_URL"]);
                    node.Menu_Img_Url = Convert.ToString(dr["MENU_IMG_URL"]);
                    node.Menu_Img_Expand = Convert.ToString(dr["MENU_IMG_EXPAND"]);
                    node.Menu_Type = Convert.ToInt32(dr["MENU_TYPE"]);
                    node.Menu_Sort = Convert.ToInt32(dr["MENU_SORT"]);
                    node.Menu_Power_Id = Convert.ToInt32(dr["MENU_POWER_ID"]);
                    node.Url_Type = Convert.ToInt32(dr["URL_TYPE"]);
                    node.Role_Access_Code = Convert.ToString(dr["ROLE_ACCESS_CODE"]);
                    RootMenuList.Add(node);
                }
            }
            return RootMenuList;
        }

        /// <summary>
        /// 获取排序在最前面的根菜单
        /// </summary>
        /// <returns>返回排序在最前面根菜单</returns>
        public MenuTree GetFirstRootMenu()
        {
            string sqlFirstRoot = "select * from plat_menu_tree where fartherid=-1 order by menu_sort,id_key asc"
                + " fetch first 1 rows only";
            MenuTree node = new MenuTree();
            DataRow dr = oledb.GetDataRow(sqlFirstRoot);
            if (dr != null)
            {
                node.ID_key = Convert.ToInt32(dr["ID_KEY"]);
                node.Menu_Name = Convert.ToString(dr["MENU_NAME"]);
                node.FartherId = Convert.ToInt32(dr["FARTHERID"]);
                node.Menu_Url = Convert.ToString(dr["MENU_URL"]);
                node.Menu_Img_Url = Convert.ToString(dr["MENU_IMG_URL"]);
                node.Menu_Img_Expand = Convert.ToString(dr["MENU_IMG_EXPAND"]);
                node.Menu_Type = Convert.ToInt32(dr["MENU_TYPE"]);
                node.Menu_Sort = Convert.ToInt32(dr["MENU_SORT"]);
                node.Menu_Power_Id = Convert.ToInt32(dr["MENU_POWER_ID"]);
                node.Url_Type = Convert.ToInt32(dr["URL_TYPE"]);
                node.Role_Access_Code = Convert.ToString(dr["ROLE_ACCESS_CODE"]);
            }
            return node;
        }

        /// <summary>
        /// 根据菜单ID_KEY判断菜单是否有子菜单
        /// </summary>
        /// <param name="menuId">菜单ID</param>
        /// <returns>返回判断结果</returns>
        public bool HasChild(string menuId)
        {
            string SqlHasChild = "select count(*) from PLAT_MENU_TREE where fartherid=" + menuId;
            int i = oledb.ExecuteScalar(SqlHasChild);
            if (i <= 0)
                return false;
            else
                return true;
        }

        /// <summary>
        /// 获取指定父菜单的所有子菜单
        /// </summary>
        /// <param name="fartherId">指定的父菜单ID</param>
        /// <returns>包含所有子菜单信息的DataTable对象</returns>
        public DataTable GetRootChildList(string fartherId)
        {
            string SqlGetRootChild = "select * from plat_menu_tree where fartherid=" + fartherId + " order by menu_sort,id_key asc";
            return oledb.GetDataTable(SqlGetRootChild);
        }

        public string GetRootImgUrl(string rootId)
        {
            string SqlRootImgUrl = "select MENU_IMG_URL from plat_menu_tree where id_key=" + rootId;
            return oledb.GetDataRow(SqlRootImgUrl)[0].ToString();
        }

        /// <summary>
        /// 根据指定ID号查询该菜单的子菜单数目
        /// </summary>
        /// <param name="id_Key">指定的菜单ID_KEY</param>
        /// <returns>包含子菜单的数目</returns>
        public int GetChildCount(string id_Key)
        {
            string SqlChildCount = "select count(*) from plat_menu_tree where fartherid=" + id_Key;
            return oledb.ExecuteScalar(SqlChildCount);
        }

        /// <summary>
        /// 获取数据库中所有菜单结点
        /// </summary>
        /// <returns>包含所有菜单结点的DataTable对象</returns>
        public DataTable GetAllMenuNode()
        {
            string SqlAllMenu = "select * from plat_menu_tree order by menu_sort,id_key asc";
            return oledb.GetDataTable(SqlAllMenu);
        }

        public string GetUrlByMenuId(string id_key)
        {
            string SqlGetUrl = "select MENU_URL from plat_menu_tree where id_key=" + id_key;
            DataRow dr = oledb.GetDataRow(SqlGetUrl);
            if (dr != null)
            {
                return dr[0].ToString();
            }
            else
            {
                return "-1";
            }
        }

        /// <summary>
        /// 获取所有非叶子结点菜单
        /// </summary>
        /// <returns>包含所有非叶子结点的DataTable对象</returns>
        public DataTable GetNonleafNodes()
        {
            string SqlNonleaf = "select * from plat_menu_tree where menu_type<2 order by menu_sort,id_key asc";
            return oledb.GetDataTable(SqlNonleaf);
        }

        /// <summary>
        /// 根据菜单ID获取菜单信息
        /// </summary>
        /// <param name="id_Key">菜单ID</param>
        /// <returns>返回包含菜单信息的对象</returns>
        public MenuTree GetMenuNodeById(string id_Key)
        {
            string SqlMenuNode = "select * from plat_menu_tree where id_key=" + id_Key;
            MenuTree MenuNode = new MenuTree();
            DataRow drMenuNode = oledb.GetDataRow(SqlMenuNode);
            if (drMenuNode != null)
            {
                MenuNode.ID_key = Convert.ToInt32(id_Key);
                MenuNode.Menu_Name = drMenuNode["MENU_NAME"].ToString();
                MenuNode.FartherId = Convert.ToInt32(drMenuNode["FARTHERID"]);
                MenuNode.Menu_Url = drMenuNode["MENU_URL"].ToString();
                MenuNode.Menu_Img_Url = drMenuNode["MENU_IMG_URL"].ToString();
                MenuNode.Menu_Img_Expand = drMenuNode["MENU_IMG_EXPAND"].ToString();
                MenuNode.Menu_Type = Convert.ToInt32(drMenuNode["MENU_TYPE"]);
                MenuNode.Menu_Sort = Convert.ToInt32(drMenuNode["MENU_SORT"]);
                MenuNode.Menu_Power_Id = Convert.ToInt32(drMenuNode["MENU_POWER_ID"]);
                MenuNode.Url_Type = Convert.ToInt32(drMenuNode["URL_TYPE"]);
                MenuNode.Role_Access_Code = drMenuNode["ROLE_ACCESS_CODE"].ToString();
            }
            return MenuNode;
        }

        /// <summary>
        /// 向数据库中插入菜单项
        /// </summary>
        /// <param name="newMenuNode">包含插入信息的对象</param>
        /// <returns>操作影响行数</returns>
        public int InsertMenu(MenuTree newMenuNode)
        {
            StringBuilder sb = new StringBuilder("insert into plat_menu_tree(menu_name,fartherid,menu_url,menu_img_url"
                + ",menu_img_expand,menu_type,menu_sort,menu_power_id) values ('");
            sb.Append(newMenuNode.Menu_Name + "',");
            sb.Append(newMenuNode.FartherId + ",'");
            sb.Append(newMenuNode.Menu_Url + "','");
            sb.Append(newMenuNode.Menu_Img_Url + "','");
            sb.Append(newMenuNode.Menu_Img_Expand + "',");
            sb.Append(newMenuNode.Menu_Type + ",");
            sb.Append(newMenuNode.Menu_Sort + ",");
            sb.Append(newMenuNode.Menu_Power_Id + ")");
            int i = oledb.ExecuteNonQuery(sb.ToString());
            if (i == 1)
            {
                string SqlGetId = "select id_key from plat_menu_tree where fartherid="
                    + newMenuNode.FartherId + " and menu_name='" + newMenuNode.Menu_Name + "'";
                newMenuNode.ID_key = Convert.ToInt32(oledb.GetDataRow(SqlGetId)[0]);
            }
            return i;
        }

        public void GetChildList(string id_key,List<string> childList)
        {
            string SqlGetChild = "select id_key from plat_menu_tree where fartherid=" + id_key;
            DataTable dtChild = oledb.GetDataTable(SqlGetChild);
            if (dtChild != null)
            {
                for (int i = 0; i < dtChild.Rows.Count; i++)
                {
                    string childid = dtChild.Rows[i][0].ToString();
                    childList.Add(childid);
                    GetChildList(childid, childList);
                }
            } 
        }

        /// <summary>
        /// 更新数据库中菜单项
        /// </summary>
        /// <param name="newMenuNode"></param>
        /// <returns></returns>
        public int UpdateMenu(MenuTree newMenuNode)
        {
            StringBuilder sbUpdate = new StringBuilder("update plat_menu_tree set menu_name='");
            sbUpdate.Append(newMenuNode.Menu_Name + "',fartherid=");
            sbUpdate.Append(newMenuNode.FartherId + ",menu_url='");
            sbUpdate.Append(newMenuNode.Menu_Url + "',menu_img_url='");
            sbUpdate.Append(newMenuNode.Menu_Img_Url + "',menu_img_expand='");
            sbUpdate.Append(newMenuNode.Menu_Img_Expand + "',menu_type=");
            sbUpdate.Append(newMenuNode.Menu_Type + ",menu_sort=");
            sbUpdate.Append(newMenuNode.Menu_Sort + ",menu_power_id=");
            sbUpdate.Append(newMenuNode.Menu_Power_Id + " where id_key=");
            sbUpdate.Append(newMenuNode.ID_key);
            return oledb.ExecuteNonQuery(sbUpdate.ToString());
        }

        /// <summary>
        /// 根据指定ID号删除该菜单
        /// </summary>
        /// <param name="id_Key">指定的菜单ID_KEY</param>
        /// <returns>操作是否成功</returns>
        public bool DeleteMenuNode(string id_Key)
        {
            string SqlDelMenu = "delete from plat_menu_tree where id_key=" + id_Key;
            if (oledb.ExecuteNonQuery(SqlDelMenu) == 1)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 获取错误信息
        /// </summary>
        /// <returns></returns>
        public string GetErrorMessage()
        {
            return oledb.ErrInfo;
        }
    }
}
